package LawsonTransformJava.pgm;
import java.sql.*;
import java.io.*;
import java.lang.reflect.InvocationTargetException;

import LawsonTransformJava.xref.CrossRef;
import LawsonTransformJava.dao.DAOFactoryDB;
import LawsonTransformJava.dao.DAOPayDeductn;
import LawsonTransformJava.dao.DAObject;
import LawsonTransformJava.data.TblPayDeductn;
import LawsonTransformJava.main.ClassMain;

public class PR560RecType2 extends DAObject implements 
    DAOPayDeductn, Runnable {

  	private TblPayDeductn pyd = new TblPayDeductn();

	private int checkId = 0;
	private PRHistReport errReport = null;
	private FileWriter errOutput = null;
	private String prtDept = null;
	
	
    public PR560RecType2 (File output, FileWriter errOutput, PRHistReport errReport, boolean allowBlanks) throws SQLException {
    // initialization 
		try {
			//Append data to FileWriter = true
			this.output = new FileWriter(this.outputFile = output, true);
			this.errReport = errReport;
			this.errOutput = errOutput;
			
			//initialize
			init(allowBlanks);
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
    }
    
    public PR560RecType2 (FileWriter outputFile, FileWriter errOutput, PRHistReport errReport, boolean allowBlanks) throws SQLException {
    // initialization 
		try {
			this.output = outputFile;
			this.errReport = errReport;
			this.errOutput = errOutput;
			
			//initialize
			init(allowBlanks);
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
  }

    public PR560RecType2 (FileWriter outputFile, FileWriter errOutput, PRHistReport errReport, boolean allowBlanks, String department) throws SQLException {
    // initialization 
		try {
			this.output = outputFile;
			this.errReport = errReport;
			this.errOutput = errOutput;
			/* PYD does not have a populated department. Department is required for a valid ProcessLevel lookup */
			this.prtDept = department;
			
			//initialize
			init(allowBlanks);
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
  }

  private void init(boolean allowBlanks) throws IOException, SQLException {
	    // Set the data variables
		data = DAOFactoryDB.getDAODB(true);
	    
	    stmtData = data.getDataStatement();	    
	    countStmtData = data.getCountStatement();
  }
  
  public void setCheckId(int checkId) {
	  this.checkId=checkId;
	  this.whereClause = null;
  }
  
  public void setDepartment(String department) {
	  this.prtDept = department;
  }
  
  public TblPayDeductn findPayDeductn() {
		try {
			if (whereClause==null || whereClause.equals("")) {
				whereClause = " WHERE (((" + TABLE_NAME + ".CHECK_ID)=" + checkId + "))";
			} else {
				whereClause = whereClause.trim() + " AND " + TABLE_NAME + ".CHECK_ID=" + checkId;
			}
			sqlData = "SELECT " +
					TABLE_NAME + ".COMPANY " +
					", " + TABLE_NAME + ".PROCESS_LEVEL " +
					", " + TABLE_NAME + ".EMPLOYEE " +
					", " + TABLE_NAME + ".CHECK_ID " +
					", " + TABLE_NAME + ".DED_CODE " +					
					", " + TABLE_NAME + ".CHECK_DATE " +
					", " + TABLE_NAME + ".WAGE_AMOUNT " +
					", " + TABLE_NAME + ".DED_AMT " +
					", " + TABLE_NAME + ".TAX_WAGES " +
					", " + TABLE_NAME + ".EXCESS " +
					", " + TABLE_NAME + ".WORK_STATE " +
					", " + TABLE_NAME2 + ".CASE_NUMBER" +
					", " + TABLE_NAME3 + ".NEXT_AMOUNT" +
					" FROM (" + data.getSchema() + TABLE_NAME + 
/*TABLE		*/		" LEFT JOIN " + data.getSchema() + TABLE_NAME2 +
/* JOIN	TO	*/		" ON (" + TABLE_NAME + ".COMPANY = " + TABLE_NAME2 + ".COMPANY" +
/*	TABLE2  */		")    AND (" + TABLE_NAME + ".GARN_OBJ_ID = " + TABLE_NAME2 + ".GARN_OBJ_ID))" + 
/*TABLE2	*/		" LEFT JOIN " + data.getSchema() + TABLE_NAME3 +
/* JOIN TO	*/		" ON (" + TABLE_NAME2 + ".COMPANY = " + TABLE_NAME3 + ".COMPANY" +
/*	TABLE3	*/		")    AND (" + TABLE_NAME2 + ".GARN_OBJ_ID = " + TABLE_NAME3 + ".GARN_OBJ_ID" + 
					") " + (whereClause==null?"":whereClause);
			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			if (rslt.next()){
				hasMoreRecs = true;
				pyd = (TblPayDeductn) createObject(rslt, new TblPayDeductn());
			}
			else
				hasMoreRecs = false;
			return pyd;
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
	  return null;
}

  public TblPayDeductn findPayDeductn(String employeeNumber) {
	try {
//		TblPayDeductn pyd = new TblPayDeductn();
		
		if (whereClause==null) {
			whereClause = " WHERE EMPLOYEE=" + employeeNumber + " AND CHECK_ID=" + checkId;
		} else {
			whereClause = whereClause.trim() + " AND EMPLOYEE=" + employeeNumber + " AND CHECK_ID=" + checkId;
		}
		sqlData = "SELECT * FROM " + data.getSchema() + TABLE_NAME + " " + 
				(whereClause==null?"":whereClause);
		rslt = data.getDataStatement().executeQuery(sqlData);
		rsmd = rslt.getMetaData();
		rslt.next();
		pyd = (TblPayDeductn) createObject(rslt, new TblPayDeductn());
		return pyd;
	} catch (SQLException e) {
	    // Could not connect to the database
		System.out.println("SQLException: " + e);
	} catch (InvocationTargetException ite) {
		System.out.println("InvocationException: " + ite);
	} catch (IllegalAccessException iae) {
		System.out.println("IllegalAccessException: " + iae);
	}
	return new TblPayDeductn();
  }

  public TblPayDeductn nextPayDeductn() {
//	  TblPayDeductn pyd = new TblPayDeductn();
		try {
			if (rslt.next()){
				hasMoreRecs = true;
				pyd = (TblPayDeductn) createObject(rslt, new TblPayDeductn());
			}
			else
				hasMoreRecs = false;
			return pyd;
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
	  return null;
}

	public boolean hasMore () {
	  return hasMoreRecs;
	}

  public int countPayDeductn() {
	try {
		countRecs = data.getCountStatement().executeQuery("SELECT COUNT(*) " +
				"FROM " + data.getSchema() + TABLE_NAME + " " + 
				(whereClause==null?"":whereClause));
		countRecs.next();
		return recCount=countRecs.getInt(1);
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		}
		return 0;
  }
  
  public void run() {
	  try {
		
		//@ TODO DEBUG BEGIN - remove lines
//		this.setWhereClause("WHERE EMPLOYEE=100466");
		//@ TODO DEBUG END   - remove lines
		pyd = findPayDeductn();
		while (hasMore()) {
			writePR560Layout(pyd);
			pyd = nextPayDeductn();
		}
	} catch (IOException e) {
		// Could not write properties file
		// Could not create output file
		System.out.println("IOException: " + e);
	}

  }
  
	//Output the PR560 format - Record Type 2
	public void writePR560Layout(TblPayDeductn pyd) throws IOException {
		// TODO Remove From
		/*
		// Sample calls to the xDedCodes translation object
		String [] newDedCde = xDedCodes.getValues(pyd.getDed_code());
		String newX = xDedCodes.getValues(pyd.getDed_code())[0];
		String str2 = xDedCodes.getNew(pyd.getDed_code());
		int int1 = xDedCodes.getTaxBen(pyd.getDed_code());
		String str4 = xDedCodes.xlate(pyd.getDed_code());
		*/
		// TODO Remove To

		String writeValue = null;

		writeValue =
				"2" + "," +						//RecType
				ClassMain.COMPANY + "," +
				PR560RecType1.getXrefEmployees().xlate(String.valueOf(pyd.getEmployee()))	       + "," +
//				pyd.getEmployee() + "," +																	//Employee
				"\"" + PR560RecType1.getXrefProcessLevels().xlate(pyd.getProcess_level() + (this.prtDept!=null?this.prtDept:"")) + "\"" + "," +	//Process Level
//				pyd.getProcess_level() + "," +												//Process Level
//				pyd.getProcess_level() + "," +
				"," +
				pyd.getCheck_id() + "," +
				"," +
				"\"" + PR560RecType1.getXrefDedCodes().lookupHist(pyd.getDed_code()) + "\"" + "," +
//				pyd.getDed_code() + "," +
				pyd.getCheck_date() + "," +		//Tr_date
				pyd.getCheck_date() + "," +		//Check_date
				"," +
				"," +
				"," +
				pyd.getWage_amount() + "," +
				"," +
				pyd.getDed_amt() + "," +
				pyd.getTax_wages() + "," +
				pyd.getExcess() + "," +
				"," +
				(pyd.getWork_state().equalsIgnoreCase("\"\"")?"\""+ClassMain.WORKSTATE+"\"":pyd.getWork_state()) + "," +
				"," +
				"," +
				pyd.getCase_number() + "," +	//Garnishment Case Number
				"," +							//Garnishment File Number
				pyd.getNext_amount() + "," +	//EDM Next Amount - Required for Garnishment
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				pyd.getBus_nbr_grp() + "," +
				"," +
				"," +
				"," 
			;
		//Check for Cross Reference Errors
		if (CrossRef.isError()) {
			//Increment Record in Error
			errReport.addToCounter("PYD Records in Error", 1);
			//Write to Error File
			errOutput.append(writeValue + "\n");
			errOutput.flush();
			CrossRef.resetError();
		}
		else {
			//Increment Record Written
			errReport.addToCounter("PYD Records Written", 1);
			//Write to Load File
			this.output.append(writeValue + "\n");
			this.output.flush();
		}
	}
}